DBtools.h++ is a programming interface for C++ programmers who want to access relational databases from object-oriented applications. It can be classified as middleware--it operates between the C++ application code and the proprietary database API, offering an identical, portable interface to all supported databases.
With DBtools.h++, both novice and expert C++ programmers can be more productive by taking advantage of well-designed database classes to write cleaner, more maintainable code. DBtools.h++ features a two-tiered architecture--a "core library" provides the consistent C++ programming interface for the user, with separate "access libraries" available for each database. These access libraries handle the variations among database implementations, so developers can design an application only once, then reuse it with a different database by simply changing the access libraries. By pairing the core and access libraries, DBtools.h++ encapsulates the interaction with the RDBMS.
DBtools.h++ also utilizes Tools.h++, the widely used data structure and collection class library for C++ programming. DBtools.h++ relies on the Tools.h++ data structures for managing data returned from the database to the C++ application. DBtools.h++ and Tools.h++ are integrated in such a way that data is easily moved back and forth between the native datatypes used in the various supported databases, and in-memory collections of higher-level C++ datatypes. The result is significant--data in a form that is immediately useful in the application.
DBtools.h++ delivers native access to Oracle, Sybase, Informix, and Ingres RDBMSs, and generic database access via ODBC. It is available in source code for Unix, and both source and object forms for Windows 3.1 and Windows NT. DBtools.h++ encapsulates the SQL 92 Data Manipulation Language (DML) and many Data Definition Language (DDL) constructs.
While SQL and its variants provide something of a common language for RDBMS server operations on the data banks, client applications written in C++ (or C, for that matter) do not communicate with a RDBMS server solely in terms of SQL. Logons, connections and data binding to application variables are examples of client/server communication that are beyond the scope of almost all SQL implementations. This leaves the programmer with three basic options:
Use a preprocessor-based mechanism to establish and maintain links with a server.
Use the proprietary call-level interface (CLI) that some RDBMS vendors make available.
Use the generic call-level interface known as ODBC that Microsoft has championed for its Windows operating system.
Each of these is an awkward alternative for the C++ developer interested in portability, and, equally important, all are procedural rather than object-oriented.
DBtools.h++ addresses these key issues by providing a way to encapsulate the procedural Call Level Interfaces (CLI) of proprietary databases and ODBC with an intuitive C++ interface. A look at the DBtools.h++ architecture and a comparison of the various coding styles will illustrate the benefits of the object-oriented DBtools.h++ middleware layer.
As noted above, DBtools.h++ uses a two-tiered architecture to provide a single, common API for a variety of databases. The DBtools.h++ Core Library encapsulates key database concepts in a database-independent manner following the SQL standards. Each class within the Core Library has a well-defined API which is invariant across specific database implementations.
Each DBtools.h++ Access Library corresponds to its specific database API, and implements the full set of classes required by the Core Library. Classes within the Access Libraries provide working methods for the Core Library's classes. One can think of the Core Library as a "software backplane" into which any Access Library can be plugged.
Specifically, the Core Library contains a set of interface classes and a corresponding set of "stubbed" implementation classes. Only the interface classes are visible to the application programmer. The stubbed implementation classes are base classes for a family of derived implementation classes that are database-specific and are part of the Access Libraries. Because database-specific functions and datatypes are handled automatically by the Access Libraries, the resulting code is portable, and the interface is simpler and more intuitive for the developer. However, DBtools.h++ does not prevent access to the native database API, should that be necessary.
DBtools.h++ uses a Producer/Product paradigm to create objects. To ensure correct typing of objects, all database-specific object instances are ultimately obtained from a database object of the same type. This is done "behind the scenes," using private rather than public constructors. The Producer/Product hierarchy is shown in Figure 2 (each class is produced by the class above it at the higher level):
RWDBManager
RWDBDatabase
RWDBConnection
RWDBSystemHandle
RWDBCursor
RWDBPREhraseBook
RWDBResult
RWDBSelector
RWDBCursor
RWDBReader
RWDBTable
RWDBCursor
RWDBDeleter
RWDBInserter
RWDBReader
RWDBUPREdater
Use of this paradigm ensures that all objects with database-specific variants are correctly typed. An Oracle RWDB Database can only produce Oracle RWDBTables; a Sybase RWDBTable can only produce Sybase RWDBReaders, and so on.
In all cases, objects such as connections, handles, and cursors will be produced by a specific RWDB Database declared by the developer. RWDBDatabase is specialized for the underlying database being used. One advantage of this approach is that there is no way to inadvertently use a Sybase-specific object with an Oracle database, for example.
The following examples illustrate the key benefit of a middleware product such as DBtools.h++: increased productivity. DBtools.h++ improves productivity in a variety of ways, including:
Reduced coding and training time--the API is simple and intuitive for those accustomed to database programming. Developers focus their time on business objectives rather than arcane database features and functions.
Flexibility among team members--no need to train programmers specifically for working with a particular database.
Portability--developers code programs once and port them, often without even relinking (in the case of DLLs).
Reduced maintenance time--more resources can be deployed for creating new solutions, with fewer resources spent maintaining old ones.
To demonstrate these benefits, we present a comparative example that shows the straightforward nature of the DBtools.h++ API. This example selects data from a table and prints out each row. The code to accomplish this is shown as DBtools.h++ code, Oracle OCI code, and ODBC code. The table in question is shown in Table 1.
Column Name Data Type ID Integer Name String Percent Float
Example 1 with DBtools.h++ Source Code
// Select * from table1 (all rows from table1) using DBtools.h++.
#include <iostream.h>
#include <rw/db/db.h>
int anInt;
RWCString aString;
float aFloat;
void main ()
{
RWDBDatabase aDB = // Open the Database
RWDBManager::database ("ODBC", "Q+E_paradox", "", "", "c:\\paradat");
RWDBTable aTable = aDB.table("TABLE1"); // Get the table
RWDBSelector aSelector = aDB.selector(); // Get a selector
aSelector << aTable; // Specifies SELECT * FROM (every row)
RWDBReader aReader = aSelector.reader(); // Get a reader, and
while (aReader()) // print out each row
{
aReader >> anInt >> aString >> aFloat;
cout << anInt << '\t' << aString << '\t' << aFloat << endl;
}
}
Note: This DBtools.h++ code example is portable to any supported database simply by changing the RWDBManager line
Now, let's look at using Oracle's OCI to do the same, simple select * from.
Example 1 with Oracle OCI Code
TESTSIMP.C
/* Comparison code, using Oracle's OCI to do a simple select * from table t1 */
#include <stdio.h>
#include <ctype.h>
#include <malloc.h>
#include <string.h>
#include <stdlib.h>
#include <oci.h>
#include <iostream.h>
/* The necessary cursor. */
struct cda_def cursorStruct;
struct cda_def *cursor = &cursorStruct;
#define ASTRING_LEN 31 /* Space for the '\0'! */
int anInt;
char aString[ASTRING_LEN];
float aFloat;
char *selectString = "SELECT * FROM t1";
Lda_Def lda;
text hda[256];
int getOff()
{
ologof(&lda);
return -1;
}
int main(int argc, char **argv)
{
int ret;
char *uid = "SCOTT/TIGER";
/* LOGON TO ORACLE */
if (orlon(&lda, hda, (text *) uid, -1, 0, -1, -1))
return getOff();
/* OPEN A CURSOR */
if (oopen(cursor, &lda, (text *)0, -1, -1, (text *)0, -1))
return getOff();
if (osql3(cursor, (text *) selectString, -1))
return getOff();
/* BIND OUTPUT BUFFERS */
if (odefin(cursor, 1, (ub1 *)&anInt, sizeof(int), OCI_EINT,
-1, (sb2 *)0, (text *)0, -1, -1, (ub2 *)0, (ub2 *)0 )
|| odefin(cursor, 2, (ub1 *)aString, ASTRING_LEN, OCI_ESTR,
-1, (sb2 *)0, (text *)0, -1, -1, (ub2 *)0, (ub2 *)0 )
|| odefin(cursor, 3,(ub1 *)&aFloat, sizeof(float), OCI_EFLT,
-1, (sb2 *)0, (text *)0, -1, -1, (ub2 *)0, (ub2 *)0 ))
return getOff();
if (oexec(cursor))
return getOff();
/* FETCH AND PRINT ROWS */
while(!ofetch(cursor))
cout << anInt << '\t' << aString << '\t' << aFloat << endl;
ologof(&lda);
return 0;
}
This example shows the general complexity of the OCI syntax, as compared to DBtools.h++. The function calls are not intuitive (e.g. osql3, odefin), and the interface is definitely not object-oriented. A few specific features are also worth noting. For example, at the beginning of the example, we needed to define a cursor in order to select from a table using OCI. With DBtools.h++, the use of a selector is sufficient. Note too that we needed to define a string that adds space for the null character that is added to the end of a string by OCI. This is an example of data not being consistent across databases, requiring programmers to be aware of the differences and account for them if they want their programs to operate with more than one database.
Next we'll look at the same example written in ODBC code.
ODBCSEL1.CPP
#include <windows.h>
#include <odbcinst.h>
#include <w16macro.h>
#include <sql.h>
#include <sqlext.h>
#include <iostream.h>
#define STRING_LEN 30
HENV henv;
HDBC hdbc;
HSTMT hstmt;
RETCODE retcode;
int anInt;
UCHAR aString[STRING_LEN];
float aFloat;
SDWORD cbInt, cbString, cbFloat;
void main()
{
retcode = SQLAllocEnv(&henv); /* Environment handle */
if (retcode != SQL_SUCCESS)
return; /* Error */
retcode = SQLAllocConnect(henv, &hdbc);/* Connection handle */
if (retcode != SQL_SUCCESS)
return; /* Error */
/* Set login timeout to 5 seconds. */
SQLSetConnectOption(hdbc, SQL_LOGIN_TIMEOUT, 5);
/* Connect to data source */
retcode = SQLConnect(hdbc, "Q+E_paradox", SQL_NTS,
"", SQL_NTS, "", SQL_NTS);
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
return; /* Error */
/* Process data after successful connection */
retcode = SQLAllocStmt(hdbc, &hstmt); /* Statement handle */
/* Execute the select statement. */
retcode = SQLExecDirect(hstmt, "SELECT * FROM TABLE1", SQL_NTS);
if (retcode == SQL_SUCCESS)
{
while (TRUE)
{
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR)
return; /*Error condition*/
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{ /* Get and print out the data for columns 1, 2, and 3 */
/* Print the row of data */
SQLGetData(hstmt, 1, SQL_C_DEFAULT, &anInt, 0, &cbInt);
SQLGetData(hstmt, 2, SQL_C_CHAR, aString, STRING_LEN, &cbString);
SQLGetData(hstmt, 3, SQL_C_FLOAT, &aFloat, 0, &cbFloat);
cout << anInt << '\t' << aString << '\t' << aFloat << endl;
} else
break;
}
}
SQLFreeStmt(hstmt, SQL_DROP);
SQLDisconnect(hdbc);
SQLFreeConnect(hdbc);
SQLFreeEnv(henv);
}
Again, the general syntax here is more complex, requiring more detailed coding on the programmer's part. Specifically, the developer is required to get both an environment handle and a connection, two operations handled automatically by DBtools.h++. Printing the results of the select also takes considerably more code. And, both ODBC and OCI require that you allocate memory yourself--if you allocate less than you need, you risk overwriting memory that's already in use. (Other vendors, such as Sybase, handle the memory allocation for you--another source of incompatibility between programs developed for different database APIs.) Developers must either know the database well enough to estimate the size of the data returned from an operation, or they must consistently over-allocate resources as insurance against memory conflicts. DBtools.h++ solves this problem for developers by using RWDBReader to create a buffer for binding data to a particular memory location. It checks for data size and automatically reserves the correct amount.
Using DBtools.h++ as a middleware product for client/server applications offers significant productivity benefits and cost savings. Table 2 shows a summary of benefits and associated features.
RWDBConnections represent a network resource that applications must be allowed to create and manage themselves. Connections are requested from a database, RWDBDatabase, and passed to other objects to specify the connection to be used.
Connections have a well-defined set of semantics and methods, including open, close, beginTransaction(), commitTransaction(), etc. Each connection knows its state and generates an error if used inappropriately.
RWDBCursor encapsulates SQL cursors. It is used to traverse a set of rows; allowing a single row to be fetched, deleted, inserted, or updated.
An RWDBCursor is produced by a variety of producers, such as RWDBDatabase and RWDBSelector.
RWDBDatabase encapsulates an open database, representing a server, a user on that server, and a database opened for that user. Databases are created by the global database manager, RWDBManager. Databases provide interfaces for tables, queries, direct SQL execution and database-level DDL (Data Definition Language) concepts. RWDBDatabases are the central “producers” for other DBtools.h++ objects, such as connections, cursors, tables, etc.
RWDBDatabases manage connections with database servers. When a database is instantiated, an initial connection is created and reserved. The reserved connection is used by the database to fill requests for which the requester has not provided a connection. The reserved connection remains open until the database is closed.
RWDBDeleter encapsulates SQL delete statements. Deleters are used to delete rows specified by a selection criterion.
RWDBInserter encapsulates SQL insert statements. It supports the direct insertion of values ("Insert into table values (...)") and insertion via select ("Insert into table select ..."). In the latter case, the table which produces the inserter is provided with a selector. In the former, the values to insert are "shifted in" with the << operator.
RWDBMemTable represents tabular data within memory. Memory tables are created by reading data from a database and storing the information in rows within memory. These tables can then be accessed by using readers in the same way that non-memory tables, i.e. result tables, are accessed.
RWDBReader provides access to the results of queries, insertions, deletions, etc. Readers can be used any time the results return tabular data in the form of a table, i.e. an RWDBTable.
RWDBResult represents a set of results returned from executing queries, insertions, etc. The set of results is comprised of a sequence of tables, each containing tabular data. DBtools.h++ recognizes that results may be returned whenever a statement is executed, therefore RWDBResult is always used as the return type from these interactions.
RWDBSelector and RWDBCompoundSelector encapsulate SQL select statements. These are used to construct simple to complex queries containing any of the SQL clauses and operators.
RWDBStoredProc is an encapsulation of a stored procedure. It is used to define a stored procedure, retrieve the definition of a stored procedure, execute a stored procedure, and process the results.
RWDBTable encapsulates the concept of tabular data or tables. This class represents a table of information whose actual location is unimportant. The table could reside on the server as a table or exist as results from a query. The application does not have to be aware of where the table is located.
Several other classes derive from RWDBTable, supplying additional semantics. These include RWDBSelector, RWDBMemTable, and RWDBResultTable.
An RWDBUpdater is an encapsulated SQL update statement. It is used to update a set of rows in a table, based on information specified by a selection criterion.
RWDBColumn encapsulates descriptions of information, either columns in a table or a parameter to a stored procedure. When used to describe columns within a table this description can then be used to create tables or to access the columns within tables. When used to describe the parameters passed to stored procedures, the description can be used to create stored procedures or to access the parameters passed. Instances of class RWDBColumn are produced solely by instances of RWDBSchema.
RWDBColumn has several different parts that describe a column or parameter. These include name, data type, native data type, storage length, numeric precision, numeric scale, null allowed indication, and stored procedure parameter type. Each column also has an associated table, which may or may not be available, based upon the method with which the column was created. An associated table allows the column to access special information, such as the underlying database’s method of comparing names.
The expression classes allow encapsulation of the "where" clause and "select" clause of an SQL statement. A programmer using DBtools.h++ writes an expression using standard C++ syntax. Rather than being immediately evaluated to a numeric, string or boolean result, the result is a parse tree that can be subsequently traversed. This enables a string to be created in the appropriate dialect for a given database. The string can then be used to create an SQL statement suitable for executing at the server.
The RWDBCriterion class is for expressions that will be evaluated as booleans by the server. Expressions of this type will have relational rather than arithmetic operators. This class assists in making a compile-time type checking of DBtools.h++ code. An RWDBAssignment is the result of applying the RWDBColumn::assign() method to an RWDBExpression. The result is an encapsulation of the SQL “SET column=expression” phrase.
There is a single, global RWDBManager in charge of producing RWDBDatabase s. The RWDBManager, through its database() method, is responsible for filling requests for database implementations of a particular type. It mediates access to database servers, abstracting details of access to database-specific libraries.
The schema class represents an ordered collection of columns, either created or retrieved. The RWDBSchema class is used to describe tables or stored procedure parameters.
This class contains specific information regarding the storage of all possible datatypes (see the section below for data types used in DBtools.h++). It provides storage for C++ primitive types, and for structured types used by DBtools.h++.
The RWDBValue class is derived from RWCollectable in order to be used by the library in RWCollections, e.g. rows within a table.
DBtools.h++ augments the built-in C++ types with additional data types. The additional datatypes come from three different Rogue Wave sources, Tools.h++, Money.h++ and DBtools.h++. These additional datatypes provide a cross-section of the most common relational database datatypes.
The datatypes used from Tools.h++ include RWCString, RWDate, and RWTime. RWCString provides an efficient and proven class for the manipulation of strings of any length. RWDate and RWTime provide portable and complete classes for the manipulation of dates and times.
RWDecimalPortable is from the Money.h++ class library. It provides a portable decimal number representation with known rounding semantics.
DBtools.h++ also provides RWDBBlob, for representing Binary Large Objects, RWDB DateTime, which jointly represents dates and times, and RWDBDuration, for representing time spans.
DBtools.h++ is a complete library of foundation classes providing object-oriented access to relational databases. It is middleware, residing between your application and the underlying database C API, replacing it with an object-oriented, component-based application design. DBtools.h++ enhances programmer productivity by delivering a common API, and seamless portability, across the leading relational databases in Unix and Windows environments.